package cn.com.dao;

import cn.com.entity.MyClass;
import cn.com.entity.TeacherScore;
import cn.com.entity.User;
import cn.com.tool.Jdbc;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Project:Academic - TeacherDao
 *
 * @author 黄俊玮 On Administrator
 * @version 1.0
 * @DataTime 2024/6/14 18:53:21
 * @since 1.8
 */
public class TeacherDao {

    public boolean show(String name,String pwd,int phone,int id){

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int i = 0;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_user where phone ="+phone+" and id != "+id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                Jdbc.close(resultSet,preparedStatement,connection);
                return false;
            }else {
                Jdbc.close(resultSet,null,null);
            }
            preparedStatement= connection.prepareStatement("UPDATE t_user SET `name` = '"+name+"',pwd= "+pwd+",phone ="+phone+" WHERE id = "+id);
            i = preparedStatement.executeUpdate();

        } catch (Exception e) {
            System.out.println("有异常抛出");
            e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }

        if ( i > 0){
            return true;
        }
        return false;
    }

    public List<MyClass> find(int id) {
        List<MyClass> list = new ArrayList<>();
        String sql = "SELECT t_task.*,t_courses.c_name,t_class.name,t_user.name AS tname  " +
                "FROM `t_task`,`t_courses`,`t_class`,`t_user`  WHERE t_task.coid = t_courses.c_id AND t_task.tid = t_user.id AND t_task.cid = t_class.id AND t_task.tid = "+id+";";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                String cid = resultSet.getString("cid");//班级id
                int coid = resultSet.getInt("coid");//课程id
                String cName = resultSet.getString("c_name");//课程名称
                String name = resultSet.getString("name");//班级名称

                list.add(new MyClass(cid,coid,cName,name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }

        return list;
    }

    public List<TeacherScore> doFind(String cid,String co_id){
        List<TeacherScore> list = new ArrayList<>();

        String sql ="select t_user.id,`t_user`.clazz,t_user.phone,t_user.name,t_courses.c_id,t_courses.c_name, t_score.scores,`t_class`.name as cname from `t_user`LEFT JOIN `t_class` ON `t_user`.clazz =`t_class`.id left join `t_score`on `t_user`.id = `t_score`.sid left join  `t_courses`on `t_courses`.c_id = `t_score`.coid where `t_user`.clazz ='"+cid+"' and t_courses.c_id = " +co_id;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String clazz = resultSet.getString("clazz");
                int user = resultSet.getInt("phone");
                String name = resultSet.getString("name");
                int coi = resultSet.getInt("c_id");
                String coname = resultSet.getString("c_name");
                double scores = resultSet.getDouble("scores");
                String cname = resultSet.getString("cname");
                list.add(new TeacherScore(id,clazz,user,name,coi,coname,scores,cname));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Jdbc.close(resultSet,preparedStatement,connection);

        return list;
    }

    public boolean modifyNow(int s_id,int co_id,double score){
        String sql = "UPDATE `t_score` SET `scores` = "+score+" WHERE sid = "+s_id+" AND coid = "+co_id+";";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
           e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }

        if (i > 0)return true;
        return false;
    }
}
